﻿--维护客服跟进的订单  需要修改成 exec sp_executesql @sql 方式将 Stype 作为字段使用
CREATE PROCEDURE [dbo].[proc_Order_Getlist_Track_Kf_20141023]
	(
		@UserName nvarchar(50),
		@Stype nvarchar(50),
		@Stext nvarchar(50),
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@StartIndex int,
		@EndIndex int,
		@typeid int --成单类型
	)
AS
begin
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,@StartDate+' 00:00:00')
	Set @eRq = Convert(Datetime,@EndDate+' 23:59:59')

	DECLARE @cSql nvarchar(4000)
	set @cSql = ''
--条件判断开始

If @typeid>0
	Set @cSql=@cSql+' AND TypeId='+Convert(varchar(10),@typeid)

If @Stext<>''
	SET @cSql=@cSql+' AND '+@Stype+' like ''%'+@Stext+'%'' '

--条件判断结束

	Set @cSql=@cSql+')'
	Set @cSql='Declare @sRq Datetime,@eRq Datetime
Set @sRq = Convert(Datetime,''' + @StartDate + ' 00:00:00'')
Set @eRq = Convert(Datetime,''' + @EndDate + ' 23:59:59'')
;WITH list As(Select ROW_NUMBER() OVER (ORDER BY [Order].StateId, [Order].CreateDate DESC)AS Row
		,[Order].Id
		,OId
		,IsNull(OrderId,'''') As OrderId
		,CusId
		,CusName
		,TypeName
		,Salesman
		,SalesmanAccount
		,TradeDate
		,[Order].CreateDate
		,IsNull(YingShouPrice,0) As YingShouPrice
		,IsNull(ShiShouPrice,0) As ShiShouPrice
		,IsGreen
		,[Order].StateId
		,IsNULL((Select Title From Order_State Where Id=[Order].StateId),''---'') As StateName
		,TrackKfName
		,[Order].CompanyId
		,ExCompanyId
		,TrackKfName as servicepersonEName
		,noe_flag
		--,Isnull((select Pizhu from Customer_PiZhu where CustomerId=CusId),'''') as Pizhu --批注内容字段 2015.08.20 
		--,Isnull((select CusState from Customer_PiZhu where CustomerId=CusId),0) as CusState --是否有批注字段 
		From [Order]
		Where TrackKfAccount='''+@UserName+''' AND CreateDate>=@sRq And CreateDate<=@eRq '+@cSql
			
	Set @cSql=@cSql+'Select *,(Select Count(0) From list) As RecordCount From list Where Row Between '+Convert(varchar(10),@StartIndex)+' and '+Convert(varchar(10),@EndIndex) + ' Order By Row'
	Exec(@cSql)
	
	--;WITH list As(Select ROW_NUMBER() OVER (ORDER BY [Order].StateId, [Order].CreateDate DESC)AS Row
	--	,[Order].Id
	--	,OId
	--	,IsNull(OrderId,'') As OrderId
	--	,CusId
	--	,CusName
	--	,TypeName
	--	,Salesman
	--	,SalesmanAccount
	--	,TradeDate
	--	,[Order].CreateDate
	--	,IsNull(YingShouPrice,0) As YingShouPrice
	--	,IsNull(ShiShouPrice,0) As ShiShouPrice
	--	,IsGreen
	--	,[Order].StateId
	--	,IsNULL((Select Title From Order_State Where Id=[Order].StateId),'---') As StateName
	--	,TrackKfName
	--	,[Order].CompanyId
	--	,ExCompanyId
	--	,TrackKfName as servicepersonEName,
	--	noe_flag
	--	From [Order]
	--	Where 
		
	--	TrackKfAccount=@UserName and (typeid=@typeid or @typeid=0)
	--	And [Order].CreateDate>=@sRq And [Order].CreateDate<=@eRq
	--	And (@Stext='' Or CusName like '%'+@Stext+'%')
	--)

	--Select *,(Select Count(0) From list) As RecordCount From list Where Row Between @StartIndex and @EndIndex Order By Row
end
